package xyh.ContributionSystem.ProjectContribution;

import xyh.ContributionSystem.ContributionSearch.SearchContribution;
//import xyh.DAO.DAOBase;
import Connection.DAOBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ProjectContributionDAOImpl extends DAOBase implements ProjectContributionDAO  {
    private static final String CONTRIBUTION_INSERT_SQL = "INSERT INTO pro_con(contribution_id,student_id,p_type,p_id,p_time,p_task,contribution_outlay) VALUES(?,?,?,?,?,?,?)";
    private static final String CONTRIBUTION_UPDATE_SQL = "UPDATE pro_con SET student_id = ?, p_type = ?,p_id = ?,p_time = ?,p_task = ?,contribution_outlay = ? WHERE contribution_id = ?";
    private static final String CONTRIBUTION_SELECT_SQL = "SELECT * FROM pro_con WHERE contribution_id = ?";
    private static final String CONTRIBUTION_SELECT_ALL_SQL = "SELECT * FROM pro_con";

    @Override
    public boolean addProjectContribution(ProjectContribution projectContribution) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(CONTRIBUTION_INSERT_SQL);
            psmt.setInt(1, projectContribution.getContribution_id());
            psmt.setInt(2, projectContribution.getStudent_id());
            psmt.setString(3, projectContribution.getP_type());
            psmt.setInt(4, projectContribution.getP_id());
            psmt.setString(5, projectContribution.getP_time());
            psmt.setString(6, projectContribution.getP_task());
            psmt.setDouble(7, projectContribution.getContribution_outlay());
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updateProjectContribution(ProjectContribution projectContribution) {
        Connection con = null;
        try {
            con = getConnection();
            // 创建预编译语句对象
            PreparedStatement psmt = con.prepareStatement(CONTRIBUTION_UPDATE_SQL);
            // 设置参数值
            psmt.setInt(1, projectContribution.getStudent_id());
            psmt.setString(2, projectContribution.getP_type());
            psmt.setInt(3, projectContribution.getP_id());
            psmt.setString(4, projectContribution.getP_time());
            psmt.setString(5, projectContribution.getP_task());
            psmt.setDouble(6, projectContribution.getContribution_outlay());
            psmt.setInt(7, projectContribution.getContribution_id());
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public ProjectContribution getProjectContribution(int contribution_id){
        Connection con = null;
        ProjectContribution projectContribution = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(CONTRIBUTION_SELECT_SQL);
            psmt.setInt(1, contribution_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                int student_id = rs.getInt("student_id");
                String p_type = rs.getString("p_type");
                int p_id = rs.getInt("p_id");
                String p_time = rs.getString("p_time");
                String p_task = rs.getString("p_task");
                double contribution_outlay = rs.getDouble("contribution_outlay");
                projectContribution = new ProjectContribution(contribution_id, student_id, p_type, p_id, p_time, p_task, contribution_outlay);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return projectContribution;
    }

    @Override
    public List<ProjectContribution> getAllProjectContribution() {
        List<ProjectContribution> projectContributions = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(CONTRIBUTION_SELECT_ALL_SQL);
            while (rs.next()) {
                int contribution_id = rs.getInt("contribution_id");
                int student_id = rs.getInt("student_id");
                String p_type = rs.getString("p_type");
                int p_id = rs.getInt("p_id");
                String p_time = rs.getString("p_time");
                String p_task = rs.getString("p_task");
                double contribution_outlay = rs.getDouble("contribution_outlay");
                ProjectContribution projectContribution = new ProjectContribution(contribution_id, student_id, p_type, p_id, p_time, p_task, contribution_outlay);
                projectContributions.add(projectContribution);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return projectContributions;
    }

    @Override
    public List<ProjectContribution> findProjectContribution(SearchContribution searchContribution) {
        List<ProjectContribution> contributions = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM pro_con WHERE 1=1");
            if (searchContribution.getContribution_id() != 0) {
                sqlBuilder.append(" AND contribution_id = ?");
            }
            if (searchContribution.getStudent_id() != 0) {
                sqlBuilder.append(" AND student_id = ?");
            }
            if (searchContribution.getP_type() != null) {
                sqlBuilder.append(" AND p_type LIKE ?");
            }
            if (searchContribution.getP_id() != 0) {
                sqlBuilder.append(" AND p_id = ?");
            }
            if (searchContribution.getP_time() != null) {
                sqlBuilder.append(" AND p_time = ?");
            }
            if (searchContribution.getP_task() != null) {
                sqlBuilder.append(" AND p_task LIKE ?");
            }
            if (searchContribution.getContribution_outlay() > 0) {
                sqlBuilder.append(" AND contribution_outlay = ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchContribution.getContribution_id() != 0) {
                psmt.setInt(index++, searchContribution.getContribution_id());
            }
            if (searchContribution.getStudent_id() != 0) {
                psmt.setInt(index++, searchContribution.getStudent_id());
            }
            if (searchContribution.getP_type() != null) {
                psmt.setString(index++, "%" + searchContribution.getP_type() + "%");
            }
            if (searchContribution.getP_id() != 0) {
                psmt.setInt(index++, searchContribution.getP_id());
            }
            if (searchContribution.getP_time() != null) {
                psmt.setString(index++, searchContribution.getP_time());
            }
            if (searchContribution.getP_task() != null) {
                psmt.setString(index++, "%" + searchContribution.getP_task() + "%");
            }
            if (searchContribution.getContribution_outlay() > 0) {
                psmt.setDouble(index++, searchContribution.getContribution_outlay());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                int contribution_id = rs.getInt("contribution_id");
                int student_id = rs.getInt("student_id");
                String p_type = rs.getString("p_type");
                int p_id = rs.getInt("p_id");
                String p_time = rs.getString("p_time");
                String p_task = rs.getString("p_task");
                double contribution_outlay = rs.getDouble("contribution_outlay");
                ProjectContribution Contribution = new ProjectContribution(contribution_id, student_id, p_type, p_id, p_time, p_task, contribution_outlay);
                contributions.add(Contribution);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return contributions;
    }
}
